﻿using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Dapper; 
using System.Linq;
using System.Data.SqlClient;

namespace ServiceCore.Tool.Dapper
{

    public class Dapper_Helper : IDapper_Helper
    {
        private readonly IConfiguration AppSettingConfig;
        public Dapper_Helper(IConfiguration configuration)
        {
            AppSettingConfig = configuration;
        }
        public string ReadSqlConnection { get { return AppSettingConfig["connection:read"]; } }
        public string WriteSqlConnection { get { return AppSettingConfig["connection:write"]; } } 
        public string RkSqlConnection { get { return AppSettingConfig["connection:rksql"]; } }

        public string Rk47SqlConnection { get { return AppSettingConfig["connection:rk47sql"]; } }



        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ResultListModel<T> GetRKPageList<T>(string MasterSql, string where, string orderby, object serchobj, bool? IsPage, int? page, int? pagesize, string sort = "desc")
        {

            ResultListModel<T> result = new ResultListModel<T>();
            page = page.HasValue ? page.Value : 0;
            pagesize = pagesize.HasValue ? pagesize.Value : 10;
            int total_count = 0;
            using (IDbConnection dapperconn = new SqlConnection(RkSqlConnection))
            {
                StringBuilder sql = new StringBuilder();
                StringBuilder sql_getRowCount = new StringBuilder();
                string orderStr = $" order by {orderby} {sort}";
                if (!IsPage.HasValue || IsPage.Value)
                {
                    sql.Append($"{MasterSql} {where} {orderStr}");
                    sql.AppendFormat(" offset {0} rows", (pagesize * (page - 1)));
                    sql.AppendFormat(" fetch next {0} rows only", pagesize);
                }
                else
                {
                    sql.Append($"{MasterSql} {where} {orderStr}");
                }
                sql_getRowCount.Append($"select count(*) from ({MasterSql} {where}) t");
                total_count = dapperconn.Query<int>(sql_getRowCount.ToString(), serchobj).SingleOrDefault();
                var result_list = dapperconn.Query<T>(sql.ToString(), serchobj).ToList();
                result.code = 0;
                result.msg = "success";
                result.rows = result_list;
                result.total = total_count;
                result.current = page.Value;
                return result;
            }
        }
        /// <summary>
        /// 建议操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int do_rk47_Execute(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(Rk47SqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Execute(query, param);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行操作异常", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> Get_rk47_List<T>(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(Rk47SqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Query<T>(query, param);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行查询异常==>" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 建议操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int do_rk_Execute(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(RkSqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Execute(query, param);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行操作异常=>"+ ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> Get_rk_List<T>(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(RkSqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Query<T>(query, param, commandTimeout: 60);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行查询异常==>" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }



        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public object GetList(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Query(query, param);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行查询异常", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 建议操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int do_Execute(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(WriteSqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Execute(query, param);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行操作异常", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> GetList<T>(string query, object param)
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Query<T>(query, param);
                }
                catch (Exception ex)
                {
                    throw new Exception("执行查询异常==>" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> GetList<T>(string[] where, object param, string orderby = "", string desc = "")
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                StringBuilder query = new StringBuilder();
                string tableName = typeof(T).ToString();
                query.AppendFormat("select {0}  from  `{1}` ", "*", tableName.Substring(tableName.LastIndexOf('.') + 1));
                if (where != null && where.Count() > 0)
                {
                    query.AppendFormat(" where 1=1 and " + string.Join(" and ", where));
                }
                if (!string.IsNullOrEmpty(orderby))
                {
                    query.AppendFormat(" order by {0}", orderby);
                }
                if (!string.IsNullOrEmpty(desc))
                {
                    query.AppendFormat(" {0}", desc);
                }
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Query<T>(query.ToString(), param);
                }
                catch (Exception ex)
                {
                    throw new Exception("获取列表异常:" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> GetList<T>(string[] where, object param, int pageindex, int pagesize, string orderby = "", string desc = "")
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                StringBuilder query = new StringBuilder();
                string tableName = typeof(T).ToString();
                query.AppendFormat("select {0}  from `{1}` ", "*", tableName.Substring(tableName.LastIndexOf('.') + 1));
                if (where != null && where.Count() > 0)
                {
                    query.AppendFormat(" where 1=1 and " + string.Join(" and ", where));
                }
                if (!string.IsNullOrEmpty(orderby))
                {
                    query.AppendFormat(" order by {0}", orderby);
                }
                if (!string.IsNullOrEmpty(desc))
                {
                    query.AppendFormat(" {0}", desc);
                }

                query.AppendFormat(" offset {0} rows", (pagesize * (pageindex - 1)));
                query.AppendFormat(" fetch next {0} rows only", pagesize);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Query<T>(query.ToString(), param);
                }
                catch (Exception ex)
                {
                    throw new Exception("获取列表异常", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public T GetModel<T>(string[] where, object param)
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                StringBuilder query = new StringBuilder();
                string tableName = typeof(T).ToString();
                query.AppendFormat("select {0}  from dbo.[{1}] ", "*", tableName.Substring(tableName.LastIndexOf('.') + 1));
                if (where.Count() > 0)
                {
                    query.AppendFormat(" where 1=1 and " + string.Join(" and ", where));
                } 
                try
                {
                    return conn.Query<T>(query.ToString(), param).FirstOrDefault();
                }
                catch (Exception ex)
                {
                    throw new Exception("获取实体异常=>" + ex.ToString(), ex);
                }
                finally
                {

                }
            }
        }

        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public T GetRKModel<T>(string[] where, object param)
        {
            using (IDbConnection conn = new SqlConnection(RkSqlConnection))
            {
                StringBuilder query = new StringBuilder();
                string tableName = typeof(T).ToString();
                query.AppendFormat("select {0}  from dbo.[{1}] ", "*", tableName.Substring(tableName.LastIndexOf('.') + 1));
                if (where.Count() > 0)
                {
                    query.AppendFormat(" where 1=1 and " + string.Join(" and ", where));
                }
                try
                {
                    return conn.Query<T>(query.ToString(), param).FirstOrDefault();
                }
                catch (Exception ex)
                {
                    throw new Exception("获取实体异常=>" + ex.ToString(), ex);
                }
                finally
                {

                }
            }
        }

        /// <summary>
        /// 简易查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount<T>(string[] where, object param)
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                StringBuilder query = new StringBuilder();
                string tableName = typeof(T).ToString();
                query.AppendFormat("select count({0})  from `{1}` ", "*", tableName.Substring(tableName.LastIndexOf('.') + 1));
                if (where.Count() > 0)
                {
                    query.AppendFormat(" where 1=1 and " + string.Join(" and ", where));
                }

                try
                {
                    return conn.Query<int>(query.ToString(), param).FirstOrDefault();

                }
                catch (Exception ex)
                {

                    throw new Exception("获取实体异常", ex);

                }
                finally
                {

                }
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">搜索条件</param>
        /// <param name="orderby">单排序字段</param>
        /// <param name="isdesc">正序 倒序</param>
        /// <param name="pageindex">分页页数</param>
        /// <param name="pagesize">分页数量</param>
        /// <param name="param">搜索条件的配套参数</param>
        /// <returns></returns>
        public IEnumerable<T> GetPageList<T>(string[] where, string orderby, bool isdesc, int pageindex, int pagesize, object param, ref int count)
        {
            using (IDbConnection conn = new SqlConnection(ReadSqlConnection))
            {
                StringBuilder query = new StringBuilder();
                StringBuilder getcount = new StringBuilder();
                string tableName = typeof(T).ToString();
                query.AppendFormat(" select {0}  from `{1}` ", "*", tableName.Substring(tableName.LastIndexOf('.') + 1));
                //getcount.AppendFormat(" select count({0}) as count  from {1} (updlock) where {0} is not NULL", IndexKey, tableName.Substring(tableName.LastIndexOf('.'))); 
                getcount.AppendFormat(" select rows FROM sysindexes WHERE id = OBJECT_ID('{0}') AND indid < 2", tableName.Substring(tableName.LastIndexOf('.')));
                if (where != null && where.Count() > 0)
                {
                    query.AppendFormat(" where 1=1 and " + string.Join(" and ", where));
                }
                if (!string.IsNullOrEmpty(orderby))
                {
                    query.AppendFormat(" order by {0}", orderby);
                    if (isdesc)
                    {
                        query.AppendFormat(" desc");
                    }
                    else
                    {
                        query.AppendFormat(" asc");
                    }
                }
                query.AppendFormat(" offset {0} rows", (pagesize * (pageindex - 1)));
                query.AppendFormat(" fetch next {0} rows only", pagesize);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    var q = conn.QueryMultiple(getcount.ToString() + query.ToString(), param);
                    count = q.Read<Count>().FirstOrDefault().rows;
                    return q.Read<T>();
                }
                catch (Exception ex)
                {
                    throw new Exception("查询分页列表信息异常", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        private class Count
        {
            public int rows { get; set; }
        }
        /// <summary>
        /// 添加单条数据生成SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        public string AddBySqlStr<T>()
        { 
            string tableName = typeof(T).ToString();
            string fieldName = string.Join(",", typeof(T).GetProperties().Where(s => s.Name != "OrderDetails" && s.Name != "StateName" && s.Name != "GroupName").Select(s => s.Name));
            string UsefieldName = fieldName.Replace("index,", "[index],").Replace("Key,", "[Key],");
            string Params_fieldName = "@" + fieldName.Replace(",", ",@");
            StringBuilder query = new StringBuilder();
            query.AppendFormat("insert into  [dbo].[{0}] ({1}) values ({2})", tableName.Substring(tableName.LastIndexOf('.') + 1), UsefieldName, Params_fieldName);
            return query.ToString();
        }
        /// <summary>
        /// 添加单条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        public int Add<T>(T Entity)
        {
            using (IDbConnection conn = new SqlConnection(WriteSqlConnection))
            {
                string query = AddBySqlStr<T>();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Execute(query.ToString(), Entity);
                }
                catch (Exception ex)
                {
                    throw new Exception("添加数据异常=>" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 批量添加数据(少批量)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Entity"></param>
        /// <returns></returns>
        public int Add<T>(List<T> EntityList)
        {
            using (IDbConnection conn = new SqlConnection(WriteSqlConnection))
            {
                string query = AddBySqlStr<T>();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Execute(query.ToString(), EntityList);
                }
                catch (Exception ex)
                {
                    throw new Exception("添加数据异常=>" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        ///  数据更新
        /// </summary>
        /// <typeparam name="T">表明更新的什么表</typeparam>
        /// <param name="set">设置需要更新的字段</param>
        /// <param name="where">更新条件 为空则更新所有  或者则形如"navigation_id=@navigation_id"</param>
        /// <param name="param">参数 如需多条件批量更新 则可以入参一个LIST</param>
        /// <returns></returns>
        public int Update<T>(string[] set, string[] where, object param)
        {
            using (IDbConnection conn = new SqlConnection(WriteSqlConnection))
            {
                if (!where.Any())
                {
                    ///不允许无条件提交更新
                    return 0;
                }
                string query = UpdateBySqlstr<T>(set, where);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    //Common.Tools.Es_Log_Helper.Add_Log($"{query.ToString()} ==> {Newtonsoft.Json.JsonConvert.SerializeObject(param)}==>{WriteSqlConnection}", "/执行一个SQL");
                    return conn.Execute(query.ToString(), param);
                }
                catch (Exception ex)
                {
                    throw new Exception("更新数据异常" + ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        ///  数据更新 只返回语句
        /// </summary>
        /// <typeparam name="T">表明更新的什么表</typeparam>
        /// <param name="set">设置需要更新的字段</param>
        /// <param name="where">更新条件 为空则更新所有  或者则形如"navigation_id=@navigation_id"</param>
        /// <param name="param">参数 如需多条件批量更新 则可以入参一个LIST</param>
        /// <returns></returns>
        public string UpdateBySqlstr<T>(string[] set, string[] where)
        {

            string tableName = typeof(T).ToString();
            if (!where.Any())
            {
                ///不允许无条件提交更新
                return "";
            }
            List<string> array = new List<string>();
            foreach (var item in set)
            {
                array.Add(item + "=@" + item);
            }
            string setStr = string.Join(",", array);
            string whereStr = "";
            if (where.Count() > 0)
            {
                whereStr = "and " + string.Join(" and ", where);
            }
            StringBuilder query = new StringBuilder();
            query.AppendFormat("update {0} set {1} where 1=1 {2}", tableName.Substring(tableName.LastIndexOf('.') + 1), setStr, whereStr);
            return query.ToString();
        }
        /// <summary>
        /// 根据条件删除数据
        /// </summary>
        /// <typeparam name="T">表明删除的什么表</typeparam>
        /// <param name="where">更新条件 为空则更新所有  或者则形如"navigation_id=@navigation_id"</param>
        /// <param name="param">参数 如需多条件批量更新 则可以入参一个LIST</param>
        /// <returns></returns>
        public int Delete<T>(string[] where, object param)
        {
            using (IDbConnection conn = new SqlConnection(WriteSqlConnection))
            {
                string tableName = typeof(T).ToString();
                if (!where.Any())
                {
                    ///不允许无条件提交删除
                    return 0;
                }
                string whereStr = "";
                if (where.Count() > 0)
                {
                    whereStr = "and " + string.Join(" and ", where);
                }
                StringBuilder query = new StringBuilder();
                query.AppendFormat("delete from {0} where 1=1 {1}", tableName.Substring(tableName.LastIndexOf('.') + 1), whereStr);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {
                    return conn.Execute(query.ToString(), param);
                }
                catch (Exception ex)
                {
                    throw new Exception("删除数据异常", ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 启动一个简单事务
        /// </summary>
        /// <param name="SqlStrList"></param>
        /// <returns></returns>
        public int StartTran(List<DapperToTransaction_Model> SqlStrList)
        {
            using (IDbConnection conn = new SqlConnection(WriteSqlConnection))
            {
                conn.Open();
                int row = 0;
                IDbTransaction transaction = conn.BeginTransaction();
                try
                {
                    foreach (var item in SqlStrList)
                    {
                        row += conn.Execute(item.Sqlstr, item.SqlParam, transaction, null, null);
                    }
                    transaction.Commit();
                }
                catch //(Exception ex)
                {
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
                return row;
            }
        }
        /// <summary>
        /// 开始执行一个事务
        /// </summary>
        /// <param name="SqlStrList"></param>
        /// <returns></returns>
        public int StartTran(Func<SqlConnection, SqlTransaction, int> Implement, ref string error)
        {
            using (SqlConnection conn = new SqlConnection(WriteSqlConnection))
            {
                conn.Open(); 
                SqlTransaction transaction = conn.BeginTransaction();
                int row = 0;
                try
                {
                    row += Implement(conn, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    row = 0;
                    error = ex.Message;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
                return row;
            }
        }
        /// <summary>
        /// 开始执行一个事务
        /// </summary>
        /// <param name="SqlStrList"></param>
        /// <returns></returns>
        public int StartRKTran(Func<SqlConnection, SqlTransaction, int> Implement, ref string error)
        {
            using (SqlConnection conn = new SqlConnection(RkSqlConnection))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();
                int row = 0;
                try
                {
                    row += Implement(conn, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    row = 0;
                    error = ex.Message;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
                return row;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ResultListModel<T> GetPageList<T>(string MasterSql, string where, string orderby, object serchobj, bool? IsPage, int? page, int? pagesize, string sort = "desc")
        {

            ResultListModel<T> result = new ResultListModel<T>();
            page = page.HasValue ? page.Value : 1;
            pagesize = pagesize.HasValue ? pagesize.Value : 10;
            int total_count = 0;
            using (IDbConnection dapperconn = new SqlConnection(ReadSqlConnection))
            {
                StringBuilder sql = new StringBuilder();
                StringBuilder sql_getRowCount = new StringBuilder();
                string orderStr = $" order by {orderby} {sort}";
                if (!IsPage.HasValue || IsPage.Value)
                {
                    sql.Append($"{MasterSql} {where} {orderStr}");
                    sql.AppendFormat(" offset {0} rows", (pagesize * (page - 1)));
                    sql.AppendFormat(" fetch next {0} rows only", pagesize);
                }
                else
                {
                    sql.Append($"{MasterSql} {where} {orderStr}");
                }
                sql_getRowCount.Append($"select count(*) from ({MasterSql} {where}) t"); 
                total_count = dapperconn.Query<int>(sql_getRowCount.ToString(), serchobj).SingleOrDefault();
                string info = sql.ToString();
                var result_list = dapperconn.Query<T>(sql.ToString(), serchobj).ToList();
                result.current = page.Value;
                result.total = total_count;
                result.rows = result_list;
                return result;
            }
        }
        public class DapperToTransaction_Model
        {
            /// <summary>
            /// sql语句
            /// </summary>
            public string Sqlstr { get; set; }
            /// <summary>
            /// 参数
            /// </summary>
            public object SqlParam { get; set; }
        }
    }
}
